#!/use/bin/python
# coding=utf-8
# 服务交互-计算弹窗情况
import datetime
import pymysql

from dbutils.pooled_db import PooledDB


# proactive_service_conf 数据源
def getConfConnection():
    # 开发环境
    # pool = PooledDB(pymysql, 1, host='172.20.135.96', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_conf',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.150.109', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                db='proactive_service_conf', port=3307)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# proactive_service_data 数据源
def getDataConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.151.80', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_data',port=3306)  # 1为连接池里的最少连接数

    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_data', port=3407)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

#根据传入的SQL 返回执行SQL返回的数量
def selectNumBySql(sql,conf=2):
    if conf == 1:
        conn, cur = getConfConnection()
    else:
        conn, cur = getDataConnection()
    #print(sql)
    cur.execute(sql)
    numResult = cur.fetchone()
    num = 0
    if numResult is not None:
        num = numResult[0]
    return num


#计算百分比 保留两位小数  如:34.88%
# X为分子 Y为分母
def getRateByXY(X,Y):
    rate = 0
    if(X is None or Y is None):
        return rate
    if Y != 0:
        if X > Y:
            rate = 100
        else:
            rate = round(X / Y, 4) * 100
    return rate

#服务交互-计算弹窗情况
def insertShowRate(date):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_inter_actify_number 表是否已经存在当天 数据
        countNum = selectNumBySql(f"select count(1) num from analysis_inter_actify_number t where 1=1  and t.ref_date =  '{date}'")
        # 获取当日 弹窗总次数
        show_num = selectNumBySql(
            f"select show_num from analysis_inter_actify_number t where t.ref_date = '{date}'")
        # 获取当日 弹窗成功次数
        show_success_num = selectNumBySql(
            f"select show_success_num from analysis_inter_actify_number t where t.ref_date = '{date}'")
        # 获取当日 重试弹窗出现次数
        show_retry_num = selectNumBySql(
            f"select show_retry_num from analysis_inter_actify_number t where t.ref_date = '{date}'")
        # 获取当日 重试失败弹窗出现次数
        show_retry_fail_num = selectNumBySql(
            f"select show_retry_fail_num from analysis_inter_actify_number t where t.ref_date = '{date}'")
        # 获取当日 服务结束次数
        show_end_num = selectNumBySql(
            f"select show_end_num from analysis_inter_actify_number t where t.ref_date = '{date}'")
        # 获取当日 服务成功结束次数
        show_end_success_num = selectNumBySql(
            f"select show_end_success_num from analysis_inter_actify_number t where t.ref_date = '{date}'")
        #弹窗成功率
        show_success_rate = getRateByXY(show_success_num, show_num)
        #“重试弹窗”出现率
        show_retry_rate = getRateByXY(show_retry_num,show_num)
        #“重试失败弹窗”出现率
        show_retry_fail_rate = getRateByXY(show_retry_fail_num,show_num)
        #服务正常结束率
        show_end_success_rate = getRateByXY(show_end_success_num,show_end_num)
        if countNum == 0:
            logDetail = f"{date}日新增-弹窗成功率为{show_success_rate},“重试弹窗”出现率为{show_retry_rate},“重试失败弹窗”出现率为{show_retry_fail_rate},服务正常结束率为{show_end_success_rate}"
            print(logDetail)
            # 插入 服务交互 弹窗情况
            insertSql = f"""insert into analysis_inter_actify_number(show_success_rate,show_retry_rate,show_retry_fail_rate,show_end_success_rate,ref_date) 
                value({show_success_rate},{show_retry_rate},{show_retry_fail_rate},{show_end_success_rate},'{date}')"""
            cur.execute(insertSql)
        else:
            logDetail = f"{date}日更新-弹窗成功率为{show_success_rate},“重试弹窗”出现率为{show_retry_rate},“重试失败弹窗”出现率为{show_retry_fail_rate},服务正常结束率为{show_end_success_rate}"
            print(logDetail)
            # 更新 服务交互 弹窗情况
            updateSql = f"""
                update analysis_inter_actify_number set show_success_rate = {show_success_rate} ,show_retry_rate = {show_retry_rate},show_retry_fail_rate ={show_retry_fail_rate},
                    show_end_success_rate = {show_end_success_rate}, last_update_time = now()  where ref_date = '{date}' 
            """
            cur.execute(updateSql)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 获取当前日期
def todayYMD():
    today = datetime.datetime.now()-1
    # 获取想要的日期的时间
    re_date = (today).strftime('%Y-%m-%d')
    return re_date


# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m-%d')
    return re_date


if __name__ == '__main__':
    d = 1
    date = getdate(d)
    print (f"{date}日期,执行<服务交互-计算弹窗情况>报表")
    insertShowRate(date)
